package jdbcdemo;

//STEP 1. ����sql��صİ�

import indi.mozping.entity.Player;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * static String userName = "Lebron James";
 * static String test = "or 1 = 1 ";
 * static String SQL_INJECT = "SELECT * FROM tb_player where playName ='" + userName + "' +'" + test + "'";
 */

public class JdbcDemo {

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://192.168.11.27:3306/demo?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true";
    static final String USER = "root";
    static final String PASS = "introcks1234";

    static Statement stmt = null;
    static PreparedStatement preparedStatement = null;
    static Connection conn = null;

//    public static void main(String[] args) {
//        String nameRight = "Lebron James"; //ģ���û�������ȷ������
//        String fakeName = "Lebron Jamesxxx' or '1 = 1 "; //ģ���û�������������
//        int resultOfRightNameStatement = searchByName(nameRight, false); //ʹ��Statement��ѯ��ȷ������
//        int resultOfFakeNameStatement = searchByName(fakeName, false); //ʹ��Statement��ѯ���������
//        int resultOfRightNamePs = searchByName(nameRight, true); //ʹ��PreparedStatement��ѯ��ȷ������
//        int resultOfFakeNamePs = searchByName(fakeName, true); //ʹ��PreparedStatement��ѯ���������
//        System.out.println("ʹ��Statement��ѯ��ȷ��sql, ��ѯ����Ϊ:" + resultOfRightNameStatement);
//        System.out.println("ʹ��Statement��ѯ�����sql,��ѯ����Ϊ:" + resultOfFakeNameStatement);
//        System.out.println("ʹ��PreparedStatement��ѯ��ȷ��sql, ��ѯ����Ϊ:" + resultOfRightNamePs);
//        System.out.println("ʹ��PreparedStatement��ѯ�����sql,��ѯ����Ϊ:" + resultOfFakeNamePs);
//    }

    public static int searchByName(String username, boolean safe) {
        int count = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
            String sql;
            ResultSet rs = null;
            if (safe) {
                //ʹ��PreparedStatement
                sql = "SELECT * FROM tb_player where playName= ?";
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1, username);
                System.out.println("��ӡsql:" + preparedStatement.toString());
                rs = preparedStatement.executeQuery();
            } else {
                //ʹ��Statement
                sql = "SELECT * FROM tb_player where playName='" + username + "'";
                Statement statement = conn.createStatement();
                System.out.println("��ӡsql:" + sql);
                rs = statement.executeQuery(sql);
            }
            if (rs != null) {
                while (rs.next()) {
                    count++;
                }
            }
            return count;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return -1;
    }


    public static int executeSqlWithPreparedStatement(String sql) {
        int count = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
            preparedStatement = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                count++;
            }
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return 0;
    }


    @Test
    public void QueryStatementDemo() {
        Connection conn = null;
        Statement stmt = null;
        List<Player> players = new ArrayList<>();
        try {
            // STEP 2: ע��mysql������
            Class.forName("com.mysql.jdbc.Driver");

            // STEP 3: ���һ������
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // STEP 4: ����һ����ѯ
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String userName = "Lebron James";
            String test = "or 1 = 1 ";
            String sql_one = "SELECT * FROM tb_player where playName='" + userName + "'";

            System.out.println("sql_one:  " + sql_one);

            ResultSet rs = stmt.executeQuery(sql_one);

            // STEP 5: ��resultSet�л�ȡ���ݲ�ת����bean
            while (rs.next()) {
                System.out.println("------------------------------");
                // Retrieve by column name
                Player player = new Player();
                player.setId(rs.getInt("id"));
                player.setPlayName(rs.getString("playName"));
                player.setPlayNo(rs.getInt("playNo"));
                player.setTeam(rs.getString("team"));

                System.out.println(player.toString());

                players.add(player);
            }
            // STEP 6: �ر�����
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }

        System.out.println("----------- there are " + players.size() + " users in the list!");
    }


    @Test
    public void QueryPreparedStatementDemo() {
        Connection conn = null;
        PreparedStatement stmt = null;
        List<Player> players = new ArrayList<>();
        try {
            // STEP 2: ע��mysql������
            Class.forName("com.mysql.jdbc.Driver");

            // STEP 3: ���һ������
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // STEP 4: ����һ����ѯ
            System.out.println("Creating statement...");
            String sql = "SELECT * FROM tb_player where playName= ? ";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, "Lebron James");
            System.out.println(stmt.toString());//��ӡsql
            ResultSet rs = stmt.executeQuery();


            // STEP 5: ��resultSet�л�ȡ���ݲ�ת����bean
            while (rs.next()) {
                System.out.println("------------------------------");
                // Retrieve by column name
                Player player = new Player();
                player.setId(rs.getInt("id"));
                player.setPlayName(rs.getString("playName"));
                player.setPlayNo(rs.getInt("playNo"));
                player.setTeam(rs.getString("team"));

                System.out.println(player.toString());

                players.add(player);
            }
            // STEP 6: �ر�����
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        System.out.println("-------------------------");
        System.out.println("there are " + players.size() + " users in the list!");
    }

    @Test
    public void updateDemo() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            // STEP 2: ע��mysql������
            Class.forName("com.mysql.jdbc.Driver");

            // STEP 3: ���һ������
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // STEP 4: �����ֶ��ύ
            conn.setAutoCommit(false);

            // STEP 5: ����һ������
            System.out.println("Creating statement...");
            String sql = "update tb_player  set playNo= ? where playName= ? ";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 8);
            stmt.setString(2, "Kobe Brayent");
            System.out.println(stmt.toString());//��ӡsql
            int ret = stmt.executeUpdate();
            System.out.println("�˴��޸�Ӱ�����ݿ������Ϊ��" + ret);

            // STEP 6: �ֶ��ύ����
            conn.commit();

            // STEP 7: �ر�����
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            try {
                conn.rollback();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            se.printStackTrace();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }


}
